﻿using System;
using System.Collections.Generic;
using System.Text;
using DotNet.Model;
using DotNet.Common;
using DotNet.Data.Factory;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
namespace DotNet.Data
{
	public class ProgramDal
	{
		IDBHelp db = DBHelp.Create();
		 Hashtable htSyn = Hashtable.Synchronized(new Hashtable());
		StringBuilder strSql = new StringBuilder();

		#region 是否存在该记录
		/// <summary>
		/// 是否存在该记录
		/// </summary>
		/// <param name="programId">标识列ID</param>
		public bool Exists(int programId)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
			strSql.Append("select count(1) from [Program] where programId=@programid ");
			htSyn.Add("@programid", programId);
			return db.IsExists(strSql.ToString(), htSyn);
		}
		#endregion

		#region 得到该表数据列表
		/// <summary>
		/// 得到该表数据列表
		/// </summary>
		public IList<Program> GetModel()
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
            strSql.Append("Select [programDate],[programEdate],[programId],[programClassId],[programSort],[programName],[productID] FROM [Program] order by [programSort] asc");
			 return getProgramBySql(strSql.ToString());
		}
		#endregion

		#region 根据标识列查询实体
		/// <summary>
		/// 根据标识列查询实体
		/// </summary>
		/// <param name="programId">标识列ID</param>
		/// <returns></returns>
		public Program GetModel(int programid)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
			strSql.Append("select [programDate],[programEdate],[programId],[programClassId],[programSort],[programName],[productID] FROM [Program]");
			strSql.Append(" where programId=@programid ");
			htSyn.Add("@programid", programid);
			DataTable dt = db.GetDataTable(strSql.ToString(), htSyn);
			Program programModel = new Program();
			if (dt.Rows.Count > 0)
			{
				programModel.programDate=Convert.ToDateTime(dt.Rows[0]["programDate"]);
				programModel.programEdate=Convert.ToDateTime(dt.Rows[0]["programEdate"]);
				programModel.programClassId=Convert.ToInt32(dt.Rows[0]["programClassId"]);
				programModel.programSort=Convert.ToInt32(dt.Rows[0]["programSort"]);	
				programModel.programName=dt.Rows[0]["programName"].ToString();
				programModel.productID=dt.Rows[0]["productID"].ToString();
			}
			return programModel;
		}
		#endregion

        #region 根据类别获得数据列表
        /// <summary>
        /// 根据类别获得数据列表
        /// </summary>
        /// <param name="programClassId"></param>
        /// <returns></returns>
        public IList<Program> GetModelByClass(int programClassId)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
            strSql.Append("Select [programDate],[programEdate],[programId],[programClassId],[programSort],[programName],[productID] FROM [Program] where [programClassId]=@programclassid order by [programSort] asc");
            htSyn.Add("@programclassid",programClassId);
            return getProgramBySql(strSql.ToString(), htSyn);
		}
		#endregion       

        #region 修改信息
        /// <summary>
		/// 修改信息
		/// </summary>
		/// <param name="Program">实体</param>
		/// <returns></returns>
		public bool Update(Program programModel)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
			strSql.Append("update [Program] set ");
			strSql.Append("[programDate]=@programdate,");
			strSql.Append("[programEdate]=@programedate,");
			strSql.Append("[programClassId]=@programclassid,");
			strSql.Append("[programSort]=@programsort,");			
			strSql.Append("[programName]=@programname,");
			strSql.Append("[productID]=@productid");			
			strSql.Append(" where programId=@programid ");
			htSyn.Add("@programdate", programModel.programDate);
			htSyn.Add("@programedate", programModel.programEdate);
			htSyn.Add("@programid", programModel.programId);
			htSyn.Add("@programclassid", programModel.programClassId);
			htSyn.Add("@programsort", programModel.programSort);
			
	
			htSyn.Add("@programname", programModel.programName);
			htSyn.Add("@productid", programModel.productID);
			
			if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
			{
				return true;
			}
			return false;
		}
		#endregion

        #region 批量移动
        /// <summary>
        /// 批量移动
        /// </summary>
        /// <param name="id"></param>
        /// <param name="programClassId"></param>
        /// <returns></returns>
        public bool Update(string[] id,string programClassId)
        {
            strSql.Remove(0, strSql.Length);
            strSql.Append("Update [Program] set");
            strSql.Append("[programClassId]=@programclassid");
            htSyn.Clear();
            htSyn.Add("@programclassid", programClassId);
            strSql.Append(" where ");
            foreach (string pid in id)
            {
                strSql.AppendFormat(" programId={0} or", pid);
            }
            strSql = strSql.Remove(strSql.Length - 1, 1);
            strSql = strSql.Remove(strSql.Length - 1, 1);
            if (db.ExecuteNonQuery(strSql.ToString(),htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 删除信息
        /// <summary>
		/// 删除信息
		/// </summary>
		/// <param name="programId">标识列ID</param>
		/// <returns></returns>
		public bool Delete(int programId)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
			strSql.Append("delete from [Program] where programId=@programid");
			htSyn.Add("programid", programId);
			if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
			{
				return true;
			}
			return false;
		}
		#endregion

        #region 批量删除
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="pId"></param>
        /// <returns></returns>
        public bool Delete(string[] pId)
        {
            strSql.Remove(0, strSql.Length);
            strSql.Append("delete from [Program] where");
            htSyn.Clear();
            foreach (string id in pId)
            {
                strSql.AppendFormat(" programId={0} or", id);
            }
            strSql = strSql.Remove(strSql.Length - 1, 1);
            strSql = strSql.Remove(strSql.Length - 1, 1);
            if (db.ExecuteNonQuery(strSql.ToString())>0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 添加信息
        /// <summary>
		/// 添加信息
		/// </summary>
		/// <param name="Program">实体</param>
		/// <returns></returns>
		public bool Insert(Program programModel)
		{
			htSyn.Clear();
			strSql.Remove(0, strSql.Length);
			strSql.Append("insert into [Program]([programDate],[programEdate],[programClassId],[programSort],[programName],[productID]) values (@programdate,@programedate,@programclassid,@programsort,@programname,@productid)");
			htSyn.Add("@programdate", programModel.programDate);
			htSyn.Add("@programedate", programModel.programEdate);
			htSyn.Add("@programclassid", programModel.programClassId);
			htSyn.Add("@programsort", programModel.programSort);
			
			htSyn.Add("@programname", programModel.programName);
			htSyn.Add("@productid", programModel.productID);
			
			if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
			{
				return true;
			}
			return false;
		}
		#endregion

		#region 根据SQL语句获取集合
		/// <summary>
		///根据SQL语句获取集合
		/// </summary>
		/// <param name="strSql">sql语句</param>
		public IList<Program> getProgramBySql(string strSql)
		{
			IList<Program> list = new List<Program>();
			DataTable dt = db.GetDataTable(strSql);
			if (dt.Rows.Count > 0)
			{
				foreach (DataRow dr in dt.Rows)
				{
					Program programModel = new Program();
					programModel.programDate= Convert.ToDateTime(dr["programDate"]);
					programModel.programEdate= Convert.ToDateTime(dr["programEdate"]);
					programModel.programId= Convert.ToInt32(dr["programId"]);
					programModel.programClassId= Convert.ToInt32(dr["programClassId"]);
					programModel.programSort= Convert.ToInt32(dr["programSort"]);
					
					programModel.programName=dr["programName"].ToString();
					programModel.productID=dr["productID"].ToString();
					
					list.Add(programModel);
				}
			}
			return list;
		}
		#endregion

        #region 根据SQL语句获取集合
        /// <summary>
        ///根据SQL语句获取集合
        /// </summary>
        /// <param name="strSql">sql语句</param>
        public IList<Program> getProgramBySql(string strSql,Hashtable ht)
        {
            IList<Program> list = new List<Program>();
            DataTable dt = db.GetDataTable(strSql,ht);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    Program programModel = new Program();
                    programModel.programDate = Convert.ToDateTime(dr["programDate"]);
                    programModel.programEdate = Convert.ToDateTime(dr["programEdate"]);
                    programModel.programId = Convert.ToInt32(dr["programId"]);
                    programModel.programClassId = Convert.ToInt32(dr["programClassId"]);
                    programModel.programSort = Convert.ToInt32(dr["programSort"]);

                    programModel.programName = dr["programName"].ToString();
                    programModel.productID = dr["productID"].ToString();

                    list.Add(programModel);
                }
            }
            return list;
        }
        #endregion

        #region 根据项目类别id得到数量
        /// <summary>
        /// 根据项目类别id得到数量
        /// </summary>
        /// <param name="programClassID"></param>
        /// <returns></returns>
        public int countByProgramClass(int programClassID)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select count(programId) from [Program] where programClassId=@pcid");
            htSyn.Add("@pcid", programClassID);
            return db.GetRecordCount(strSql.ToString(),htSyn);
        }
        #endregion

        #region 项目分页
        /// <summary>
        /// 项目分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="programClassId"></param>
        /// <returns></returns>
        public DataTable pagingForProgram(int pageIndex,int pageSize,int programClassId) 
        {
            strSql.Remove(0, strSql.Length);
            if (programClassId>0)
            {
                strSql.AppendFormat(" and programClassId={0}", programClassId);
            }
            return db.GetMPageDataTable(pageIndex, pageSize, "[programDate],[programEdate],[programId],[programClassId],[programSort],[programName],[productID]", "Program", strSql.ToString(), "programSort", false);
        }
        #endregion

        #region 获得项目数量
        /// <summary>
        /// 获得项目数量
        /// </summary>
        /// <param name="programClassId"></param>
        /// <returns></returns>
        public int getRecordCount(int programClassId)
        {
            strSql.Remove(0, strSql.Length);
            htSyn.Clear();
            object objRecordCount = null;
            int i = 0;
            strSql.Append("select count(1) from [Program] where 1>0");
            if (programClassId>0)
            {
                strSql.Append(" and programClassId=@programclassid");
                htSyn.Add("@programclassid",programClassId);
            }
            objRecordCount = db.ExecuteScalar(strSql.ToString(), htSyn);
            if (objRecordCount!=null)
            {
                i = Convert.ToInt32(objRecordCount);
            }
            return i;
        }
        #endregion

    }
}
